/****************************************************
Matthew Hajduk
Anton K
Brian Graham
Shane Regel
Mackenzie Jordan

CS290: Principals of Software Development
Final Project 
12/13/13
****************************************************/

package twitterdba;

import twitter4j.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.ArrayList;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.*;
import java.util.*;
import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.lang.Character;

public class twitterDBAworker{
static boolean updateTweetsFlag;
	static Connection c = null;
    static Statement stmt = null;
    static ResultSet rs = null;
	static PreparedStatement prepstmt = null;
	public static String[] supportedFunctions = {"whoRetweetedMost","rateGrammar","retweetsToTweets","mostRepliedTo","dbRating"};//When you add a anew analysis Function/Method, put the name in here. This Array is used for the returnSupportedFunctions function; this list is printed out when the user has entered an invalid function name.
	twitterDBAworker(String dbFilename,String zipFilename, boolean preBuildFlag, boolean updateTweetsFlagI){//In this case, constructor would go into archive and create a DB, or accesses previously build DB based on prebuilt flag. - Anton
		 String[] currentline;
		 updateTweetsFlag = updateTweetsFlagI;
		 String createString;
		 PreparedStatement commStr = null;
		 if(preBuildFlag){
		 	//Code for accessing previously built SQL DB
		 	try{
		 	Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:"+dbFilename);
            stmt = c.createStatement();
            }
            catch(Exception e) {
				        System.out.println("Can't Accessing Database");
				        e.printStackTrace();
				    }
                	
                	
		 }else{

			try {
				 //If not prebuilt, must unzip twitter Zip archive.
				int zipErrCode = unzipArchive(zipFilename,null);//By default, tweets.csv is extracted into current directory
				Class.forName("org.sqlite.JDBC");
		        c = DriverManager.getConnection("jdbc:sqlite:"+dbFilename);  //need to updatewhere we putthe file
				createString = "drop table tweets";//Cleaning tables, jic
				stmt = c.createStatement();
				try{
					stmt.executeUpdate(createString);
					}
				catch(Exception e){
				}
				createString = "create table tweets(tweet_id, in_reply_to_status_id, in_reply_to_user_id, timestamp, source, text, retweeted_status_id, retweeted_status_user_id, retweeted_status_timestamp,expanded_url)";
				stmt.executeUpdate(createString);
				ArrayList<String[]> csvinput;
				csvinput = readCSV("tweets.csv");
				commStr=c.prepareStatement("insert into tweets values (?,?,?,?,?,?,?,?,?,?)");
				for(int i = 1; i < csvinput.size(); i++){
					currentline = csvinput.get(i);
					for(int v = 0; v < currentline.length; v++){
						commStr.setString(v+1,currentline[v]);
					}
					commStr.executeUpdate();
					createString = "";
				}
	 		}
			catch(Exception e) {
			        System.out.println("Can't Creating Table");
			        e.printStackTrace();
			}
		}

			if(updateTweetsFlag){//If upd}ate tweetsFlag is true, go to twitter, get new timeline, and put it into DB. This also involves resetting the 
				List<String[]> tweetsList = tweetList();
				String[] currentTweet;
				createString = "drop table tweets";//Cleaning tables, jic
				try{
					stmt.executeUpdate(createString);
				createString = "create table tweets(tweet_id, in_reply_to_status_id, in_reply_to_user_id, timestamp, source, text, retweeted_status_id, retweeted_status_user_id, retweeted_status_timestamp,expanded_url)";
				stmt.executeUpdate(createString);
				commStr=c.prepareStatement("insert into tweets values (?,?,?,?,?,?,?,?,?,?)");
				for(int i = 0; i < tweetsList.size(); i++){
					currentline = tweetsList.get(i);
					for(int v = 0; v < currentline.length; v++){
						commStr.setString(v+1,currentline[v]);
					}
					commStr.executeUpdate();
					createString = "";
				}
			}
			catch(Exception e) {
				        System.out.println("Can't Creating Table from Twitter Update");
				        e.printStackTrace();
					}

		}
	}

	/*
	Returns aiList of type String.
	Each String contains a comma seperated value list
	*/
	public static List<String[]> tweetList(){
		try {
				// gets Twitter instance with default credentials
				Twitter twitter = new TwitterFactory().getInstance();
				User user = twitter.verifyCredentials();
				GregorianCalendar beginTime = new GregorianCalendar(2013, Calendar.JANUARY, 1, 1, 1);
			      long oykumena = beginTime.getTime().getTime();
				List<Status> statuses = new ArrayList<Status>();
				List<Status> curStatuses = twitter.getHomeTimeline(new Paging(1,200));
				statuses.addAll(curStatuses);
				int iterPages = 2;
				while(!curStatuses.isEmpty()&&!(curStatuses==null)){
					curStatuses = twitter.getHomeTimeline(new Paging(iterPages,200));	
					statuses.addAll(curStatuses);
					iterPages++;
				}
				List<String[]> tweets = new ArrayList<String[]>();
				//System.out.println("Showing @" + user.getScreenName() + "'s home timeline.");
				for (Status status : statuses) 
				{
					if(status.getUser().getScreenName().equals("FP_Team4")){
						System.out.println("@" + status.getUser().getScreenName() + " - " +
						status.getText() + " - " + status.getCreatedAt());
						String retweetStatus = String.valueOf(status.getRetweetedStatus());
						String currentUserRetweetId = "null";
						if(!(retweetStatus.equals("null")||retweetStatus.equals("")||retweetStatus.equals("-1"))){
						currentUserRetweetId = String.valueOf(status.getRetweetedStatus().getUser().getId());
					}
					System.out.println("tweetList retweetId: " + currentUserRetweetId);
						//take our tweets with timestamps and put them into an arraylist of strings
						tweets.add(new String[] {String.valueOf(status.getId()), String.valueOf(status.getInReplyToStatusId()), String.valueOf(status.getInReplyToUserId()), String.valueOf(status.getCreatedAt()), String.valueOf(status.getSource()), String.valueOf(status.getText()), retweetStatus, currentUserRetweetId,"","" });
						System.out.println("Checking source: " + status.getText() + "\t" + status.getSource());
					}
				}
				for(int i=0;i<tweets.size();i++){
					System.out.println("tweets liip: "+Arrays.toString(tweets.get(i)));
				}
				return tweets;
			}

			catch (TwitterException te) {
				    
				te.printStackTrace();
				    System.out.println("Failed: " + te.getMessage());	
				return null;            
			}
	}
	
	/*
	function to return which user we retweeted the most
	*/
	public ArrayList<String> whoRetweetedMost(){
		String  retweet = " ";
		String  retweetCheck = " ";
		// double rtweet = 0;
		ArrayList<String> userIds = new ArrayList<String>();
		ArrayList<Integer> userRetweetCount = new ArrayList<Integer>();
		ArrayList<String> userHighestRetweets=new ArrayList<String>();
		int index;
		Integer curValue;
		try{
			ResultSet rs = stmt.executeQuery( "SELECT * FROM tweets;" );
		  	while ( rs.next() ) {
				retweet = rs.getString("retweeted_status_user_id");
				retweetCheck = rs.getString("retweeted_status_id");
				System.out.println("retweet: " + retweet);
				System.out.println("retweetCheck: " + retweetCheck);
				if(retweetCheck.equals("null")||retweetCheck.equals("")||retweetCheck.equals("-1")){
					System.out.println("IF");
				//do nothing
				}
				else{
					if(userIds.contains(retweet))
					{
						index = userIds.indexOf(retweet);
						curValue = userRetweetCount.get(index);
						curValue++;
						userRetweetCount.set(index,curValue);
					}else{//If the user is not already in the userIDs array, add the id string to userIds and add a  new count variable to the count array
						userIds.add(retweet);
						userRetweetCount.add(1);
					}
				}
			}
			rs.close(); //close result set
			if(userIds.isEmpty()){//If there have been no retweets
				ArrayList<String> errCodeArr = new ArrayList<String>();
				errCodeArr.add("There are no retweets");
				return errCodeArr;
			}
			userHighestRetweets.add(userIds.get(0));
			Integer curHighestCount = userRetweetCount.get(0);
			Integer curCount;
			for(int iter = 1; iter<=userIds.size()-1;iter++){//Find who has the highest count
				curCount = userRetweetCount.get(iter);
				if(curCount>curHighestCount){
					curCount=curHighestCount;
					userHighestRetweets.clear();
					userHighestRetweets.add(userIds.get(iter));
				}
				if(curCount==curHighestCount){
					userHighestRetweets.add(userIds.get(iter));
				}
			}
			Twitter twitter = new TwitterFactory().getInstance();
			ArrayList<String> userHighestReplyNames = new ArrayList<String>();
		
			for(int iter = 0; iter<userHighestRetweets.size();iter++){// Convert User Ids to names
				long curUserId = Long.parseLong(userHighestRetweets.get(iter));
				try{
				User curUser = twitter.showUser(curUserId);
				
				userHighestReplyNames.add(curUser.getName());
				}catch(Exception e){}
			}
			return userHighestReplyNames;

		} catch ( Exception e ) {
			System.err.println( e.getClass().getName() + ": " + e.getMessage() );
			System.exit(0);
			ArrayList<String> errCodeArr = new ArrayList<String>();
			errCodeArr.add("Error");
			return errCodeArr;
    	}
		
	}
	
	/*
	Returns how the user tweets (source) the most
	*/
	public double rateGrammar(){
		int count = 0; //count total tweets
		int index;
		ArrayList<String> wordList = new ArrayList<String>();
		char curChar = 12;
		String text;
		String currentWord = "";
		try{
			ResultSet rs = stmt.executeQuery( "SELECT * FROM tweets;" );
		  	while ( rs.next() ) {
		  		count++;
				text = rs.getString("text");
				boolean falsityCheck;
				if(text.equals("null")||text.equals("")||text.equals("-1")){
				// falsityCheck = retweet.equals("null");
					falsityCheck= true;
				}else{
				// falsityCheck= retweet.equals("");
					falsityCheck = false;
				}
				if(falsityCheck){
				//do nothing
				}
				else{//Have to parse the text eh.
					for (int iter = 0; iter<text.length();iter++) {
						curChar = text.charAt(iter);
						if(Character.isLetter(curChar)){//if the character is a letter, add it to current string, else add string to wordList if it's not empty (i.e. multiple punctuation marks) and reset string
						currentWord= (currentWord+curChar);
						} else if (!currentWord.isEmpty()) {
							wordList.add(currentWord);
							currentWord = "";
						}
					}
				}

			}
			rs.close(); //close result set
			if(wordList.isEmpty()){//If there is no text
				System.out.println("Word List is Empty");
				return -1;
			}

			//The part where we actually spellchecked
			String language = "en_GB";
			if (System.getProperties().containsKey("lang")) {
			        language = System.getProperty("lang");
			}
			double mistakes = 0;
			double answer = 0;
			Hunspell.Dictionary d = Hunspell.getInstance().getDictionary("./"+language);
			double totalWordCount = wordList.size();
			for (int iter = 0;iter<totalWordCount ;iter++ ) {
				if(d.misspelled(wordList.get(iter))){
					 mistakes++;
				}
			}
			answer =  mistakes/totalWordCount;
			return (answer) * 100;
		} catch ( Exception e ) {
			System.err.println( e.getClass().getName() + ": " + e.getMessage() );
			System.exit(0);
			return -1;
    	}
		
	}
	
	/*
	Return retweets to tweets percent
	*/
	public double retweetsToTweets(){
		String  retweet = " ";
		double rtweet = 0;
		boolean falsityCheck;
		int count = 0; //count total tweets
		int rcount = 0; //count how many tweets have been retweeted
		try{
			ResultSet rs = stmt.executeQuery( "SELECT * FROM tweets;" );
			System.out.println("First?:"+rs.getString("retweeted_status_id"));
		  	while ( rs.next() ) {
				retweet = rs.getString("retweeted_status_id");
				count ++;
				if(retweet.equals("null")||retweet.equals("")||retweet.equals("-1")){
				// falsityCheck = retweet.equals("null");
					falsityCheck= true;
				}else{
				// falsityCheck= retweet.equals("");
					falsityCheck = false;
				}
				System.out.println("retweet: " + retweet);
				System.out.println(updateTweetsFlag);
				System.out.println(falsityCheck);
				if(falsityCheck){
					//do nothing
					System.out.println("IF: " + retweet);
				}
				else{
					System.out.println("ELSE: " + retweet);
					rcount++; //add 1 to retweet count
				}
			}
			rs.close(); //close result set
			System.out.println("Retweets: " + rcount);
			System.out.println("tweets: " + count);
			rtweet = (double)rcount/(double)count;
			rtweet = rtweet * 100;
			return(rtweet);
		} catch ( Exception e ) {
			System.err.println( e.getClass().getName() + ": " + e.getMessage() );
			System.exit(0);
			return 0;
    	}
	}
	
	public ArrayList<String> mostRepliedTo(){//Return user you replied to the most 
		String  reply = " ";
		double rtweet = 0;
		ArrayList<String> userIds = new ArrayList<String>();
		ArrayList<Integer> userReplyCount = new ArrayList<Integer>();
		ArrayList<String> userHighestReply=new ArrayList<String>();
		int count = 0; //count total tweets
		int rcount = 0; //count how many tweets have been in reply to
		int index;
		Integer curValue;
		ResultSet rs = null;
		try{
			rs = stmt.executeQuery( "SELECT * FROM tweets;" );
		}catch(Exception e){System.out.println("rs query not working");}
		try{
		  	while ( rs.next() ) {
		  	try{
				reply = rs.getString("in_reply_to_user_id");
				}catch(Exception e){System.out.println("getting id error");}
				boolean falsityCheck;
				if(reply.equals("null")||reply.equals("")||reply.equals("-1")){
				// falsityCheck = retweet.equals("null");
					falsityCheck= true;
				}else{
				// falsityCheck= retweet.equals("");
					falsityCheck = false;
				}
				if(falsityCheck){
				//do nothing
				}
			else{
				rcount++; //add 1 to reply count
				if(userIds.contains(reply))
				{
					index = userIds.indexOf(reply);
					curValue = userReplyCount.get(index);
					curValue++;
					userReplyCount.set(index,curValue);
				}else{//If the user is not already in the userIDs array, add the id string to userIds and add a  new count variable to the count array
						userIds.clear();
						userIds.add(reply);
						userReplyCount.add(1);
					}
				}
			}
			}catch(Exception e){
				System.err.println( e.getClass().getName() + ": " + e.getMessage() );
			}
			try{
			rs.close(); //close result set
			}catch(Exception e){};

			if(userIds.isEmpty()){//If there have been no retweets
				ArrayList<String> errCodeArr = new ArrayList<String>();
				errCodeArr.add("There are no replies");
				return errCodeArr;
			}
			userHighestReply.add(userIds.get(0));
			Integer curHighestCount = userReplyCount.get(0);
			Integer curCount;
			for(int iter = 1; iter<=userIds.size()-1;iter++){//Find who has the highest count
			
				curCount = userReplyCount.get(iter);
				if(curCount>curHighestCount){
					curCount=curHighestCount;
					userHighestReply.clear();
					userHighestReply.add(userIds.get(iter));
				}
				if(curCount==curHighestCount){
					userHighestReply.add(userIds.get(iter));
				}
			}
			Twitter twitter = new TwitterFactory().getInstance();
			ArrayList<String> userHighestReplyNames = new ArrayList<String>();
		
			for(int iter = 0; iter<userHighestReply.size();iter++){// Convert User Ids to names
				long curUserId = Long.parseLong(userHighestReply.get(iter));
				try{
				User curUser = twitter.showUser(curUserId);
				
				userHighestReplyNames.add(curUser.getName());
				}catch(Exception e){}
			}
			return userHighestReplyNames;
	}
	
	public double dbRating(){//Returns user's dbRating based on dbRatio (hashtag-to-tweets)
		double dbRatio = 0; //start ratio at 0
		String  txt = " "; //will hold text from tweets
		int count = 0;
		int hashCount = 0;
		try{
			ResultSet rs = stmt.executeQuery( "SELECT * FROM tweets;" );
		  	while ( rs.next() ) {
				txt = rs.getString("text");//look at text of tweets
				count++;//add 1 to tweet count
				char[] x = txt.toCharArray(); //convert to char array to look at each character in the string
				
				for(int i=0; i < x.length; i++){
					String s = " ";
					s = s.valueOf(x[i]);
					//count hashtags
					if(s.equals("#"))
						hashCount++;
				}
			}
			rs.close(); //close result set
			dbRatio = (double)hashCount/(double)count;
			return(dbRatio);
		} catch ( Exception e ) {
			System.err.println( e.getClass().getName() + ": " + e.getMessage() );
			System.exit(0);
			return 0;
    	}	
	}
	
	public static String[] getSupportedFunctions(){
		return supportedFunctions;
	}

	public static ArrayList<String[]> readCSV(String filename){
		String csvFile = filename;
		BufferedReader br = null;
		String line = "";
		String cvsSplitBy = "\",\"";
		ArrayList<String[]> retStrList = new ArrayList<String[]>();
 
	try {
		br = new BufferedReader(new FileReader(csvFile));
		String[] currentLine;
		while ((line = br.readLine()) != null) {
			currentLine = line.split(cvsSplitBy);
 			retStrList.add(currentLine);
		}
 
	} catch (FileNotFoundException e) {
		e.printStackTrace();
	} catch (IOException e) {
		e.printStackTrace();
	} finally {
		if (br != null) {
			try {
				br.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
 	 }	
	return retStrList;
	}
	
	

	public static int unzipArchive(String zipName, String outputFolder){
		int errCode = 0;
		byte[] buffer = new byte[1024];
		try{
			if(outputFolder!=null){
				File folder = new File(outputFolder);
				
				if(!folder.exists()){
					folder.mkdir();
				}
			}

			ZipInputStream zis = new ZipInputStream(new FileInputStream(zipName));
			ZipEntry ze = zis.getNextEntry();
			while(ze!=null){
				String fileName = ze.getName();
				if(fileName.compareTo("tweets.csv")==0){
					File newFile;
					if(!(outputFolder==null)){
					newFile = new File(outputFolder+File.separator+fileName);
					}else{
						newFile = new File(fileName);
					}
					FileOutputStream fos = new FileOutputStream(newFile);
					int len;
					while((len = zis.read(buffer))>0){
						fos.write(buffer, 0, len);
					}
					fos.close();
					errCode = 1;
					break;
				}
				ze = zis.getNextEntry();

			}
			zis.closeEntry();
			zis.close();
			if(errCode==0){
				System.out.println("File tweets.csv not found in archive");
				throw new IOException();
			}
			return errCode;
		}catch(IOException ex){
			System.out.println("File tweets.csv not found in archive");
			return errCode = 0;
		}
	}

}
